/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.SubjectDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Arturia
 */
public class SubjectDAO {

    public static String generateID(Connection con) {
        String id = null;
        try {
            String sql = "SELECT MAX(numericID) "
                    + "FROM (SELECT 'numericID' = "
                    + "CASE WHEN ISNUMERIC(subject_id) = '1' THEN CAST(subject_id AS INT) "
                    + "ELSE NULL END FROM t_subject) ALIAS";
            PreparedStatement pre = con.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                int iID = Integer.parseInt(id = rs.getString(1));
                id = String.valueOf(iID + 1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public static ArrayList LoadDatabase(Connection con, SubjectDTO subject) {
        ArrayList<SubjectDTO> result = new ArrayList<SubjectDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (subject == null) {
                sql = "SELECT * FROM t_subject";
                stm = con.prepareStatement(sql);
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                SubjectDTO subjectTemp = new SubjectDTO();
                subjectTemp = new SubjectDTO();

                subjectTemp.setStrSubjectID(rs.getString(1));

                if (rs.wasNull()) {
                    subjectTemp.setStrSubjectID(generateID(con));
                }

                subjectTemp.setStrSubjectName(rs.getString(2));

                if (rs.wasNull()) {
                    subjectTemp.setStrSubjectName("");
                }

                subjectTemp.setStrDescription(rs.getString(3));

                if(rs.wasNull()){
                    subjectTemp.setStrDescription("");
                }


                result.add(subjectTemp);
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static int insert(Connection con, SubjectDTO subject) {
        int result = 0;
        try {
            String sql = "INSERT INTO t_subject VALUES(?,?,?)";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, subject.getStrSubjectID());
            stm.setString(2, subject.getStrSubjectName());
            stm.setString(3, subject.getStrDescription());
            result = stm.executeUpdate();

            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int update(Connection con, SubjectDTO subject) {
        int result = 0;
        try {
            String query = "UPDATE t_subject SET subject_name=?, description=?"
                    + "WHERE subject_id=?";
            PreparedStatement stm = con.prepareStatement(query);
            stm.setString(1, subject.getStrSubjectName());
            stm.setString(2, subject.getStrDescription());
            stm.setString(3, subject.getStrSubjectID());

            result = stm.executeUpdate();
            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int delete(Connection con, String subjectID) {
        int result = 0;
        try {
            String sql = "DELETE FROM t_subject WHERE subject_id=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, subjectID);
            result = stm.executeUpdate();
            stm.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static ArrayList<SubjectDTO> getStudentSubjects(Connection con, String studentID){
        ArrayList<SubjectDTO> result = new ArrayList<SubjectDTO>();
        try{
            String sql = "SELECT * FROM t_subject WHERE t_subject.subject_id "
                       + "IN (SELECT subject_id FROM t_student_subject WHERE student_id = ?);";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, studentID);
            ResultSet rs = stm.executeQuery();
            while(rs.next()){
                SubjectDTO subjectTemp = new SubjectDTO();
                subjectTemp = new SubjectDTO();

                subjectTemp.setStrSubjectID(rs.getString(1));

                if (rs.wasNull()) {
                    subjectTemp.setStrSubjectID(generateID(con));
                }

                subjectTemp.setStrSubjectName(rs.getString(2));

                if (rs.wasNull()) {
                    subjectTemp.setStrSubjectName("");
                }

                subjectTemp.setStrDescription(rs.getString(3));

                if(rs.wasNull()){
                    subjectTemp.setStrDescription("");
                }
                result.add(subjectTemp);
            }
            stm.close();
            con.close();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return result;
    }

    public static ArrayList<SubjectDTO> getTeacherSubjects(Connection con, String teacherID){
        ArrayList<SubjectDTO> result = new ArrayList<SubjectDTO>();
        try{
            String sql = "SELECT * FROM t_subject WHERE t_subject.subject_id "
                       + "IN (SELECT subject_id FROM t_subject_teacher_class WHERE teacher_id = ?);";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, teacherID);
            ResultSet rs = stm.executeQuery();
            while(rs.next()){
                SubjectDTO subjectTemp = new SubjectDTO();
                subjectTemp = new SubjectDTO();

                subjectTemp.setStrSubjectID(rs.getString(1));

                if (rs.wasNull()) {
                    subjectTemp.setStrSubjectID(generateID(con));
                }

                subjectTemp.setStrSubjectName(rs.getString(2));

                if (rs.wasNull()) {
                    subjectTemp.setStrSubjectName("");
                }

                subjectTemp.setStrDescription(rs.getString(3));

                if(rs.wasNull()){
                    subjectTemp.setStrDescription("");
                }
                result.add(subjectTemp);
            }
            stm.close();
            con.close();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return result;
    }
}
